﻿using System;
using System.Collections.Generic;
using IsolationLevel = System.Transactions.IsolationLevel;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using System.Data;

namespace BDShop.DAL.Extensions
{
    public static class ContextExtension
    {
        #region support method

        internal static void SetIsolationLevel(this DbContext context, IsolationLevel isolationLevel)
        {
            string sql;

            switch (isolationLevel)
            {
                case IsolationLevel.ReadUncommitted:
                    sql = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;";
                    break;
                case IsolationLevel.ReadCommitted:
                    sql = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED;";
                    break;
                default:
                    throw new Exception("ISOLATION LEVEL is not defined in this method.");
            }

            //(context as IObjectContextAdapter).ObjectContext.ExecuteStoreCommand(sql, null);
            if (context.Database.Connection.State != ConnectionState.Open)
            {
                // Explicitly open the connection, this connection will close when context is disposed
                context.Database.Connection.Open();
            }

            context.Database.ExecuteSqlCommand(sql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <param name="sql"></param>
        /// <param name="values">anonymous class</param>
        /// <returns></returns>
        public static IEnumerable<T> ExecuteProc<T>(this DbContext context, string sql, object values)
        {
            Type t = values.GetType();
            StringBuilder paramDeclare = new StringBuilder();
            PropertyInfo[] propertyInfos = t.GetProperties();
            var parameters = new SqlParameter[propertyInfos.Length];
            var obj = new object[propertyInfos.Length];
            for (int i = 0; i < propertyInfos.Length; i++)
            {
                var val = propertyInfos[i].GetValue(values,null);
                paramDeclare.Append("@" + propertyInfos[i].Name + " = {" + i + "},");
                obj[i] = val;
            }

            // param declare @param1 = {0}, @param2 = {1}
            return context.Database.SqlQuery<T>(sql + " " + paramDeclare.ToString().Trim(','), obj);
        }

        public static void AddObjectQuantityParameter(this List<SqlParameter> paramCollection, string paramName,
                                                     object value)
        {
            var typeName = "dbo.udt_ObjectQuantity";

            if (((DataTable)value).Columns.Count != 4)
                typeName = "dbo.udt_ObjectQuantity10";

            var structureParam = new SqlParameter(paramName, SqlDbType.Structured)
            {
                Direction = ParameterDirection.Input,
                TypeName = typeName,
                Value = value ?? DBNull.Value
            };

            paramCollection.Add(structureParam);
        }

        /// <summary>
        /// hai.vu: execute database store procedure or function
        /// http://stackoverflow.com/questions/5474264/how-to-pass-parameters-to-the-dbcontext-database-executesqlcommand-method
        /// </summary>
        /// <param name="context"></param>
        /// <param name="sql"> </param>
        /// <param name="parameters"> </param>
        private static void ExecuteFunction(this DbContext context, string sql, params object[] parameters)
        {
            var paramDeclare = BuilSqlCommand(sql, parameters.Length);

            context.Database.ExecuteSqlCommand(paramDeclare.ToString(), parameters);
        }

        /// <summary>
        /// hai.vu: execute sql statement and return list of T
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private static IEnumerable<T> ExecuteQuery<T>(this DbContext context, string sql, params object[] parameters)
        {
            var paramDeclare = BuilSqlCommand(sql, parameters.Length);

            return context.Database.SqlQuery<T>(paramDeclare, parameters);
        }

        /// <summary>
        /// hai.vu: 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramLength"></param>
        /// <returns>{0}, {1}, {2}, {3}, {4}, {5}</returns>
        private static string BuilSqlCommand(string sql, int paramLength)
        {
            var paramDeclare = new StringBuilder();

            paramDeclare.Append(sql);

            // append parameter declare: {0}, {1}, {2}
            for (var i = 0; i < paramLength; i++)
            {
                paramDeclare.AppendFormat(" {{{0}}}", i);

                if (i < paramLength - 1)
                    paramDeclare.Append(",");
            }

            return paramDeclare.ToString();
        }

        /// <summary>
        /// hai.vu: 
        /// </summary>
        /// <param name="sql"></param>        
        /// <param name="parameters"> </param>
        /// <returns>@param1, @param2, @param3</returns>
        private static string BuilSqlCommand(string sql, List<SqlParameter> parameters, bool formatSQL = false)
        {
            var paramDeclare = new StringBuilder();

            if (formatSQL == false)
            {
                paramDeclare.Append(sql);
            }


            // append parameter declare: {0}, {1}, {2}
            for (var i = 0; i < parameters.Count; i++)
            {
                paramDeclare.AppendFormat(" {0}", parameters[i].ParameterName);
                if (parameters[i].Direction == System.Data.ParameterDirection.InputOutput)
                    paramDeclare.Append(" out ");

                if (i < parameters.Count - 1)
                    paramDeclare.Append(",");
            }

            if (formatSQL)
            {
                return string.Format(sql, paramDeclare.ToString());
            }

            return paramDeclare.ToString();
        }


        /// <summary>
        /// hai.vu
        /// </summary>
        /// <param name="objectList"></param>
        /// <param name="paramNameList"></param>
        /// <returns></returns>
        ///trang.le:chi support input parameter 
        private static List<SqlParameter> BuildParameterList(object[] objectList, params string[] paramNameList)
        {
            var paramCollection = new List<SqlParameter>();
            for (var i = 0; i < paramNameList.Length; i++)
            {
                paramCollection.Add(new SqlParameter()
                {
                    ParameterName = paramNameList[i],
                    Value = objectList[i] ?? DBNull.Value
                });

            }

            return paramCollection;
        }

        private static List<SqlParameter> BuildOutParameterList(List<SqlParameter> paramCollection, object[] paramNameList, object[] objectList)
        {
            for (var i = 0; i < paramNameList.Length; i++)
            {
                paramCollection.Add(new SqlParameter
                {
                    ParameterName = paramNameList[i].ToString(),
                    Value = objectList[i] ?? DBNull.Value,
                    Direction = ParameterDirection.InputOutput
                });
            }

            return paramCollection;
        }

        #endregion


        /// <summary>
        /// tien hanh update status thanh pending cac product cua domain ko duoc update trong lan fetch cuoi cung
        /// </summary>
        /// <param name="context"></param>
        /// <param name="parameters">@DommainID</param>
        public static void NotifyFinishFetchPostProcess(this DbContext context, params object[] parameters)
        {
            var paramNameList = new[]
                 {
                    "@DomainID"
                 };
            var paramCollection = BuildParameterList(parameters, paramNameList);

            //            var structureParam = new SqlParameter("@CityTable", SqlDbType.Structured)
            //            {
            //                Direction = ParameterDirection.Input,
            //                TypeName = "dbo.udt_ObjectQuantity",
            //                Value = parameters[parameters.Length - 1]
            //            };
            //            paramCollection.Add(structureParam);

            context.Database.ExecuteSqlCommand(BuilSqlCommand("EXEC spNotifyFinishFetchPostProcess", paramCollection), paramCollection.ToArray());

        }

    }
}